package dao;

import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

import org.hibernate.Query;
import org.hibernate.Session;

import pojo.Chitiethoadon;
import pojo.Sanpham;
import util.ConvertHelper;
import util.HibernateUtil;

public class ChitiethoadonDAO extends AbstractDAO<Chitiethoadon, Integer> {

	@Override
	protected Class getPOJOClass() {
		return Chitiethoadon.class;
	}

	@Override
	protected Query MakeHQL() {
		// TODO Auto-generated method stub
		return null;
	}

	public List<Sanpham> GetDanhSachSanPham() {

		Session session = HibernateUtil.getSessionFactory().openSession();

		String hql = "select cthd.sanpham from Chitiethoadon cthd "
				+ "order by cthd.sanpham.masanpham asc";
		Query selectClause = session.createQuery(hql);
		List<Sanpham> lstSanPham = selectClause.list();

		session.close();
		return lstSanPham;
	}

	public List<Sanpham> GetTopNSanPhamMoiNhat(int nTop, int nThoiGian) {

		Session session = HibernateUtil.getSessionFactory().openSession();

		String hql = "";
		Query selectClause = null;
		if (-1 == nThoiGian) {
			hql = "select sp " + "from Sanpham sp "
					+ " order by sp.ngaynhansanpham desc";
			selectClause = session.createQuery(hql);
		} else {
			Calendar now = Calendar.getInstance();
			now.add(Calendar.MONTH, -nThoiGian);
			hql = "select sp " + "from Sanpham sp "
					+ "where sp.ngaynhansanpham > :nowDate"
					+ " order by sp.ngaynhansanpham desc";

			selectClause = session.createQuery(hql);
			selectClause.setParameter("nowDate", now.getTime());
		}

		selectClause.setFirstResult(0);
		selectClause.setMaxResults(nTop);

		List<Sanpham> lstSanPham = selectClause.list();
		session.close();
		return lstSanPham;
	}

	public List<Sanpham> GetTopNSanPhamBanChayNhat(int nTop, int nThoiGian) {

		List<Sanpham> lstSanPham = new ArrayList<Sanpham>();

		Session session = HibernateUtil.getSessionFactory().openSession();
		String hql = "";
		Query query = null;

		if (-1 == nThoiGian) {
			hql = "select cthd.sanpham.masanpham, count(cthd.sanpham.masanpham) as tong "
					+ " from Chitiethoadon cthd "
					+ "group by cthd.sanpham "
					+ "order by tong desc ";
			query = session.createQuery(hql);
		} else {

			Calendar now = Calendar.getInstance();
			now.add(Calendar.MONTH, -nThoiGian);

			hql = "select cthd.sanpham.masanpham, count(cthd.sanpham.masanpham) as tong "
					+ " from Chitiethoadon cthd "
					+ "where cthd.hoadon.ngayxuat > :nowDate "
					+ "group by cthd.sanpham " + "order by tong desc ";

			query = session.createQuery(hql);
			query.setParameter("nowDate", now.getTime());
		}
		query.setFirstResult(0);
		query.setMaxResults(nTop);

		List<Object[]> result = query.list();
		// SanphamDAO spdao = new SanphamDAO();

		List<Integer> lstMasanpham = new ArrayList<Integer>();
		for (int i = 0; i < result.size(); i++) {
			if (null != result.get(i))
				lstMasanpham.add((Integer) result.get(i)[0]);
		}

		hql = "select sp from Sanpham sp where sp.masanpham in (:lstMasanpham)";
		query = session.createQuery(hql);
		query.setParameterList("lstMasanpham", lstMasanpham);

		lstSanPham = query.list();
		session.close();
		return lstSanPham;
	}

	// ------------ Thống kê Doanh Thu---------------
	public List<Object[]> GetKetQuaThongKeDoanhThuTheoSanPham(
			int maNhomThongKe, int maLoaiThongKe, String loaiThoiGian,
			int khoangThoiGian, String thoiGianTu, String thoiGianDen) {

		List<Object[]> queryResult;
		String hql = "select cthd.sanpham.masanpham, sum (cthd.soluong), sum (cthd.dongia * cthd.soluong) as tong "
				+ "from Chitiethoadon cthd ";

		// Ma Nhom Thong Ke - Ma Loai Thong Ke
		if (-1 == maNhomThongKe)
			hql = hql + " where ";
		else
			hql = hql
					+ " where cthd.sanpham.loaisanpham.maloaisanpham = :maLoaiThongKe ";

		// nếu khoảng thời gian là 0 -> tìm kiếm theo thoiGianTu và thoiGianDen
		if (loaiThoiGian.equalsIgnoreCase("MocThoiGian")) {
			hql = hql + " and cthd.hoadon.ngayxuat >= :thoiGianTu "
					+ " and cthd.hoadon.ngayxuat <= :thoiGianDen ";
		} else if (loaiThoiGian.equalsIgnoreCase("KhoangThoiGian") && -1 != khoangThoiGian) {
			hql = hql
					+ " and cthd.hoadon.ngayxuat >= :nowDateWithKhoangThoiGian ";
		}

		hql = hql + "group by cthd.sanpham, cthd.sanpham.masanpham "
				+ "order by cthd.sanpham.masanpham desc ";

		hql = hql.replace("  ", " ").replace("where and", "where");
		System.out.println(hql);
		// ----------------------
		Session session = HibernateUtil.getSessionFactory().openSession();
		Query query = null;
		query = session.createQuery(hql);

		// Set Param cho MaLoaiThongKe
		try {
			query.setParameter("maLoaiThongKe", maLoaiThongKe);
		} catch (Exception ex) {
			System.out.println("Err maLoaiThongKe");
		}
		// Set Param cho KhoangThoiGian
		try {
			Calendar now = Calendar.getInstance();
			now.add(Calendar.MONTH, -khoangThoiGian);

			query.setParameter("nowDateWithKhoangThoiGian", now.getTime());
		} catch (Exception ex) {
			System.out.println("Err nowDateWithKhoangThoiGian");
		}
		// Set Param cho thoiGianTu, thoiGianDen
		try {
			query.setParameter("thoiGianTu", ConvertHelper.ConvertStringToDate(thoiGianTu));
			query.setParameter("thoiGianDen", ConvertHelper.ConvertStringToDate(thoiGianDen));
		} catch (Exception ex) {
			System.out.println("Err thoiGianTu-thoiGianDen");
		}

		// query.setFirstResult(0);
		// query.setMaxResults(nTop);
		queryResult = query.list();
		session.close();
		// ----------------------

		return queryResult;
	}
}
